package com.robot.agent.dao;

import com.robot.agent.pojo.AgentMember;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

/**
 * member数据访问接口
 *
 * @author lin
 */
public interface AgentMemberDao extends JpaRepository<AgentMember, String>, JpaSpecificationExecutor<AgentMember> {
    /**
     * 通过area查找代理商名下的今日会员
     *
     * @param area
     * @param pageable
     * @return
     */
    @Query(value = "select * from agent.agent_member where area=?  and TO_DAYS(NOW()) - TO_DAYS(registered_at) =0 order by registered_at desc", nativeQuery = true)
    public Page<AgentMember> findAgentMembersByAreaOrderByRegisteredAtDescToday(String area, Pageable pageable);


    /**
     * 通过area查找代理商名下的最近7日会员
     *
     * @param area
     * @param pageable
     * @return
     */
    @Query(value = "select * from agent.agent_member where area=?  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(registered_at) order by registered_at desc", nativeQuery = true)
    public Page<AgentMember> findAgentMembersByAreaOrderByRegisteredAtDescWeek(String area, Pageable pageable);


    /**
     * 通过area查找代理商名下的总会员
     *
     * @param area
     * @param pageable
     * @return
     */
    @Query(value = "select * from agent.agent_member where area=? order by registered_at desc", nativeQuery = true)
    public Page<AgentMember> findAgentMembersByAreaOrderByRegisteredAtDescTotal(String area, Pageable pageable);

    /**
     * 通过agentId和memberId查找代理商名下的某一会员
     *
     * @param agentId
     * @param memberId
     * @return
     */
    public AgentMember findByAgentIdAndMemberId(String agentId, String memberId);
}
